Public Class frmQuery
    Inherits System.Windows.Forms.Form

    'Public MyDataTable As clsDataTable
    Friend MyFullTableName As String

    Friend Function BuildSQL() As String

        txtStationID.Text = fixLikeWildcards(txtStationID.Text)
        txtRCode.Text = fixLikeWildcards(txtRCode.Text)
        txtCCode.Text = fixLikeWildcards(txtCCode.Text)
        txtOther.Text = fixLikeWildcards(txtOther.Text)

        Dim WhereClause As String = ""

        For i As Integer = 1 To 7
            With CType(Choose(i, txtStationID, txtDateTime, txtPCode, txtCCode, txtRCode, txtResult, txtOther), TextBox)
                Dim FieldName As String = "Dta." & Choose(i, "Station_ID", "Date_Time", "PCode", "CCode", "RCode", "Result", cboOther.Text)
                Dim Value As String = .Text.Trim
                If i = 2 Then 'this field may have some fancy date extraction text like "EXTRACT(YEAR from Date_time)=2009"
                    If .Text.ToUpper.Contains("DATE_TIME") Then
                        FieldName = ""
                        Value = Value.Replace("Date_Time", "Dta.Date_Time")
                    End If
                End If
                If i = 7 And cboOther.Text = "Create_Update" And .Text.ToUpper.Contains("CREATE_UPDATE") Then FieldName = ""
                If Value = "ALL" Or Value = "" Then
                    'leave clause empty for this field
                ElseIf Value.StartsWith("IN", StringComparison.OrdinalIgnoreCase) And Value.Contains("%") Then
                    'replace IN clause having wildcards with multiple LIKEs
                    Dim ar() As String = Value.Replace("IN (", "").Replace(")", "").Split(",")
                    Dim LikeClause As String = ""
                    For Each s As String In ar
                        LikeClause &= IIf(LikeClause = "", "", " OR ") & FieldName & " LIKE " & s
                    Next
                    WhereClause &= IIf(WhereClause = "", "(", " AND (") & LikeClause & ")"
                Else
                    'prepend field name only and assume rest of field is formatted correctly
                    WhereClause &= IIf(WhereClause = "", "", " AND ") & FieldName & " " & Value
                End If
            End With
        Next

        If WhereClause <> "" Then WhereClause = " WHERE " & WhereClause

        Return MainForm.SelectBuildQuery(Me.cboOption.SelectedIndex, WhereClause)
    End Function

    Private Function fixLikeWildcards(ByVal s As String) As String
        Return s.Replace("..", "%").Replace("*", "%").Replace("@", "_").Replace("?", "_")
    End Function

    Private Sub btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStationID.Click, btnCCode.Click, btnDateTime.Click, btnOther.Click, btnRCode.Click, btnResult.Click, btnPCode.Click
        Try
            Dim AssistType As frmQueryAssist.enumAssist, FieldName As String
            If sender Is btnStationID Then
                AssistType = frmQueryAssist.enumAssist.StationID
                FieldName = "Station_ID"
            ElseIf sender Is btnDateTime Then
                AssistType = frmQueryAssist.enumAssist.DateTimeValue
                FieldName = "Date_Time"
            ElseIf sender Is btnPCode Then
                AssistType = frmQueryAssist.enumAssist.PCode
                FieldName = "PCode"
            ElseIf sender Is btnCCode Then
                AssistType = frmQueryAssist.enumAssist.CCode
                FieldName = "CCode"
            ElseIf sender Is btnRCode Then
                AssistType = frmQueryAssist.enumAssist.RCode
                FieldName = "RCode"
            ElseIf sender Is btnResult Then
                AssistType = frmQueryAssist.enumAssist.RealValue
                FieldName = "Result"
            ElseIf sender Is btnOther Then
                Select Case cboOther.Text.ToUpper
                    Case "LEW_PCT", "DEPTH_M", "TRACK_ID" : AssistType = frmQueryAssist.enumAssist.RealValue
                    Case "QCODE" : AssistType = frmQueryAssist.enumAssist.QCode
                    Case "AGENCY" : AssistType = frmQueryAssist.enumAssist.Agency
                    Case "SCODE", "LCODE" : AssistType = frmQueryAssist.enumAssist.Validate
                    Case "CREATE_UPDATE" : AssistType = frmQueryAssist.enumAssist.DateTimeValue
                    Case "VALIDATED" : AssistType = frmQueryAssist.enumAssist.StringValue
                End Select
                FieldName = cboOther.Text
            Else
                Exit Sub
            End If

            With New frmQueryAssist(MyFullTableName, FieldName, AssistType)
                If .ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
                    Dim txt As TextBox = CType(Controls(CType(sender, Button).Name.Replace("btn", "txt")), TextBox)
                    txt.Text = .QueryString
                End If
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Close()
    End Sub

    Private Sub btnExecute_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExecute.Click
        Try
            Project.ResetPDox(ActiveBrowser.TableName)
            MainForm.SelectData(BuildSQL, False)
            SaveControlValues(REGAPP, Me)
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        frmInfoMsg.ShowInfoMsg("Parameter Queries", "You are about to save this query so it can be reused at a later time.\n\nNote that WRDB can also embed parameters in your queries to make it more convenient to reuse them. For each named parameter you place into your query enclosed in brackets (e.g., [PCODE PROMPT]), a prompt will appear when you rerun the query which allows you to specify the desired value at that time.")

        With New SaveFileDialog
            .AddExtension = True
            .CheckPathExists = True
            .SupportMultiDottedExtensions = False
            .DefaultExt = "SQL"
            .Filter = "Saved SQL Query File (*.sql)|*.sql"
            .InitialDirectory = TestNull(Project.SQLDir, AppDataPath)
            If .ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
                Dim sw As New IO.StreamWriter(.FileName, False)
                sw.Write(BuildSQL)
                sw.Close()
                sw.Dispose()
                sw = Nothing
            End If
        End With
    End Sub

    Private Sub btnSelectAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSelectAll.Click
        SelectAll()
    End Sub

    Private Sub btnShowSQL_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShowSQL.Click
        MessageBox.Show(StringFormat(FormatSQL(BuildSQL)), "SQL String", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Sub

    Private Sub frmQuery_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'btnShowSQL.Visible = Debugger.IsAttached
        Project.ResetPDox(ActiveBrowser.TableName)
        For Each s As String In Project.DB.GetColumnList(ActiveBrowser.TableName)
            Select Case s.ToUpper
                Case "RECID", "STATION_ID", "DATE_TIME", "CCODE", "RCODE", "PCODE", "RESULT", "[RESULT]"
                Case Else
                    cboOther.Items.Add(Proper(s))
            End Select
        Next
        SelectAll()
        cboOption.SelectedIndex = 0
        GetControlValues(REGAPP, Me)
        cboOption.SelectedIndex = 0
    End Sub

    Private Sub ParseString(ByRef txt As TextBox)

        'when leave the field, take list that user typed (or was inserted by
        'assistant) and create proper SQL where clause

        'if assistant used tick marks, assume string is already properly formatted

        txt.Text = fixLikeWildcards(txt.Text)

        If txt.Text.Trim = "" Or txt.Text.ToUpper.Trim = "ALL" Then
            txt.Text = "ALL"
        Else
            If Not (txt.Text.Contains("'") Or txt.Text.ToUpper.Contains("NULL")) Then 'no quotes, so try to fix it up
                Dim ar() As String = txt.Text.Split(",")
                If ar.Length = 1 Then 'is single item
                    If txt.Text.Contains("%") Or txt.Text.Contains("_") Then
                        txt.Text = "LIKE '" & ar(0).ToUpper & "'"
                    Else
                        txt.Text = "= '" & ar(0).ToUpper & "'"
                    End If
                Else 'is multiple items, make "In" list
                    txt.Text = "In ("
                    For i As Int16 = 0 To ar.Length - 1
                        txt.Text &= IIf(i = 0, "'", ", '") & ar(i).Trim.ToUpper & "'"
                    Next
                    txt.Text &= ")"
                End If
            End If
        End If
    End Sub

    Private Sub SelectAll()
        txtStationID.Text = "ALL"
        txtDateTime.Text = "ALL"
        txtCCode.Text = "ALL"
        txtRCode.Text = "ALL"
        txtPCode.Text = "ALL"
        txtResult.Text = "ALL"
        txtOther.Text = ""
        cboOther.SelectedIndex = -1
        cboOption.SelectedIndex = 0
    End Sub


    'now use my own context menu

    'Private Sub txt_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtCCode.TextChanged, txtDateTime.TextChanged, txtOther.TextChanged, txtPCode.TextChanged, txtRCode.TextChanged, txtResult.TextChanged, txtStationID.TextChanged
    '    'if pasted list from spreadsheet (crlf or tab separated), convert to commas
    '    'for this to work, textbox multiline property must be true
    '    With CType(sender, TextBox)
    '        If .Text.EndsWith(vbCrLf) Then .Text = .Text.Substring(0, .Text.Length - 2)
    '        If .Text.EndsWith(vbCr) Then .Text = .Text.Substring(0, .Text.Length - 1)
    '        .Text = .Text.Replace(vbCrLf, ", ").Replace(vbCr, ", ").Replace(vbTab, ", ")
    '    End With
    'End Sub

    'note: old VB6 behavior of selecting entire text when first clicked is not implemented (is not standard for WinXP anymore, and is difficult to implement)

    Private Sub txt_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtCCode.Validated, txtOther.Validated, txtRCode.Validated, txtStationID.Validated, txtPCode.Validated
        'don't try to validate date-time fields
        If sender Is txtOther AndAlso cboOther.Text = "Create_Update" Then Exit Sub
        ParseString(CType(sender, TextBox))
    End Sub

    Friend Property FullTableName() As String
        Get
            Return MyFullTableName
        End Get
        Set(ByVal Value As String)
            MyFullTableName = Value
        End Set
    End Property

    Private Sub txtResult_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtResult.Validated
        'if only entered number, assume want = operator
        With txtResult
            If IsNumeric(.Text) Then
                .Text = "= " & .Text
            ElseIf .Text.Trim = "" Then
                .Text = "ALL"
            End If
        End With
    End Sub

    Private Sub txtDateTime_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtDateTime.Validated
        'if only entered date, assume want all values on that date
        With txtDateTime
            If IsDate(.Text) Then
                .Text = String.Format("BETWEEN {0} AND {1}", Project.DB.DateTime(CDate(.Text)), Project.DB.DateTime(CDate(.Text).AddDays(1).AddMinutes(-1)))
            ElseIf .Text.Trim = "" Then
                .Text = "ALL"
            End If
        End With

    End Sub

    Private Sub btnHelp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnHelp.Click
        ShowHelp(Me, "16")
    End Sub

    Private Sub btnGIS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGIS.Click
        Try
            Dim lst As New Generic.List(Of String)
            If IsMWForm Then
                'lst = CType(MainForm, frmMainMW).SelectedStations
            Else
                MainForm.CheckGisData()
                With New WRDB.GIS.frmGIS(Project, ActiveTableName, clsProject.TempData, GIS.frmGIS.enumMode.SelectStations, MainWRDB.WRDBStationTypes, Project.GisBasemapStations)
                    If .ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
                        lst = .SelectedStations
                    End If
                    .Dispose()
                End With
            End If

            If lst.Count > 0 Then
                Dim s As String = ""
                For Each sta As String In lst
                    s &= IIf(s = "", "", ", ") & "'" & sta & "'"
                Next
                txtStationID.Text = String.Format("IN ({0})", s)
            End If
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Sub ContextMenuStrip1_ItemClicked(ByVal sender As Object, ByVal e As System.Windows.Forms.ToolStripItemClickedEventArgs) Handles ContextMenuStrip1.ItemClicked
        Try
            Dim txt As TextBox = CType(sender, ContextMenuStrip).SourceControl
            With txt
                If e.ClickedItem Is mnuCut Then
                    My.Computer.Clipboard.SetText(.SelectedText, TextDataFormat.Text)
                    .Text = .Text.Remove(.SelectionStart, .SelectionLength)
                ElseIf e.ClickedItem Is mnuCopy Then
                    My.Computer.Clipboard.SetText(.SelectedText, TextDataFormat.Text)
                ElseIf e.ClickedItem Is mnuPaste Then
                    FixPasteString(txt)
                ElseIf e.ClickedItem Is mnuDelete Then
                    .Text = .Text.Remove(.SelectionStart, .SelectionLength)
                ElseIf e.ClickedItem Is mnuSelectAll Then
                    .SelectAll()
                End If
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Sub txt_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtCCode.KeyDown, txtDateTime.KeyDown, txtOther.KeyDown, txtPCode.KeyDown, txtRCode.KeyDown, txtResult.KeyDown, txtStationID.KeyDown
        If e.Control And e.KeyCode = Keys.V Then 'paste shortcut
            FixPasteString(sender)
            e.Handled = True
            My.Computer.Clipboard.Clear() 'this is necessary because for some reason will still try to paste again
        End If
    End Sub

    ''' <summary>
    ''' Replace selected text in textbox with contents of clipboard; because might paste from Excel where the values are delimited by tabs or returns, replace with commas.
    ''' </summary>
    ''' <param name="TxtBox">Active textbox to paste to</param>
    Private Sub FixPasteString(ByRef TxtBox As TextBox)
        With TxtBox
            Dim i As Integer = .SelectionStart
            .Text = .Text.Remove(.SelectionStart, .SelectionLength)
            Dim text As String = My.Computer.Clipboard.GetText(TextDataFormat.Text)
            If text.EndsWith(vbCrLf) Then text = text.Substring(0, text.Length - 2)
            If text.EndsWith(vbCr) Then text = text.Substring(0, text.Length - 1)
            text = text.Replace(vbCrLf, ", ").Replace(vbCr, ", ").Replace(vbTab, ", ")
            .Text = .Text.Insert(.SelectionStart, text).Trim
            If .Text.EndsWith(",") Then .Text = .Text.Substring(0, .Text.Length - 1)
            If i >= 0 And i <= .Text.Length Then .SelectionStart = i
        End With
    End Sub

    Private Sub cboOther_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboOther.SelectionChangeCommitted
        txtOther.Text = ""
    End Sub
End Class
